<?php

/**
 * Subclass for performing query and update operations on the 'user' table.
 *
 * 
 *
 * @package lib.model
 */ 
class UserPeer extends BaseUserPeer
{
	public static function getUserById($userId){
		$con = new MysqlDAL('propel');
		$sql = "select user.id as userId,
				user.username as userName,
				user.avatar_path as avatarPath,
				user.total_ringtone as totalRingtone,
				user.ringtone_id as ringtoneId,
				user.ringtone_name as ringtoneName
				FROM user
				WHERE user.id = ".$userId;
		return $con->doSelect($sql);
	}
	public static function getTotalRingtoneByUserId($userId){
		$con = new MysqlDAL('propel');
		$sql = "SELECT count(*) as totalRingtone
				FROM ringtone
				WHERE ringtone.rt_status = 1 
				AND ringtone.creator_id = ".$userId;
		$totalRingtone = $con->doSelect($sql);
		if(count($totalRingtone) >0){
			$totalRingtone = $totalRingtone[0]['totalRingtone'];
		}
		return $totalRingtone;
	}
	public static function getTotalRingtoneByUserIdUsed($userId){
		$con = new MysqlDAL('propel');
		$sql = "SELECT count(DISTINCT ringtone.id) as totalRingtone
				FROM ringtone
				INNER JOIN ringtone_download 
				ON ringtone.id = ringtone_download.ringtone_id
				WHERE ringtone.rt_status = 1 AND
				      ringtone.creator_id = ".$userId; 
		$totalRingtone = $con->doSelect($sql);
		if(count($totalRingtone) >0){
			$totalRingtone = $totalRingtone[0]['totalRingtone'];
		}
		return $totalRingtone;
	}
	public static function getRingtoneByUserId($userId, $time, $limit, $offset= null){
		$con = new MysqlDAL('propel');
		$con->safe($userId);
		$sql = "SELECT 
					   SQL_CALC_FOUND_ROWS
					   ringtone.id as ringtoneId,
				       ringtone.name as ringtoneName,
				       ringtone.avatar_path as avatarPath,
				       ringtone.category_id as categoryId,
				       ringtone.category_name as categoryName,
				       ringtone.creator_id as creatorId,
				       ringtone.creator_name as creatorName,
				       ringtone.duration as duration,
				       ringtone.file_path_mp3 as filePath,
				       UNIX_TIMESTAMP(ringtone.created_datetime) as createdDatetime,
				       ringtone.rt_status as rtStatus,
				       (ringtone.total_set + ringtone.total_download) as totalDownload,
				       ringtone.rbt_status as rbtStatus
				FROM ringtone
				WHERE 
				      ringtone.creator_id = ".$userId." AND ringtone.rt_status = 1";
		switch ($time){
			case 0: 
				$sql.= ' ORDER BY ringtone.created_datetime DESC';
				break;
			case 1: 
				$sql.= ' ORDER BY totalDownload DESC';
				break;
			case 2:
				$sql.= ' ORDER BY TRIM(ringtone.name)';
				break;
		}
		if($limit){
			$sql.= ' LIMIT '.$limit;	
		}
		if ($offset){
			$sql.=' OFFSET '.$offset;
		}
		return $con->doSelect($sql);
	}
	
	public static function getTotalDeleteRingtoneByAdmin($userId){
		$con = new MysqlDAL('propel');
		$sql = "SELECT count(*) as totalRingtone
				FROM ringtone
				INNER JOIN ringtone_status ON ringtone.id = ringtone_status.ringtone_id
				WHERE 
				      ringtone.creator_id = ".$userId.
					 ' AND ringtone_status.rt_approve = 2
					 AND ringtone_status.rt_delete <> 2';
		$totalRingtone = $con->doSelect($sql);
		if(count($totalRingtone) >0){
			$totalRingtone = $totalRingtone[0]['totalRingtone'];
		}
		return $totalRingtone;
	}
	public static function getDeleteRingtoneByAdmin($userId, $time, $limit, $offset= null){
		$con = new MysqlDAL('propel');
		$con->safe($userId);
		$sql = "SELECT 
					   SQL_CALC_FOUND_ROWS
					   ringtone.id as ringtoneId,
				       ringtone.name as ringtoneName,
				       ringtone.avatar_path as avatarPath,
				       ringtone.category_id as categoryId,
				       ringtone.category_name as categoryName,
				       ringtone.creator_id as creatorId,
				       ringtone.creator_name as creatorName,
				       ringtone.duration as duration,
				       ringtone.file_path_mp3 as filePath,
				       ringtone_status.rt_approve_description as approvedDescription,
				       UNIX_TIMESTAMP(ringtone.created_datetime) as createdDatetime,
				       ringtone.rt_status as rtStatus,
				       (ringtone.total_set + ringtone.total_download) as totalDownload,
				       ringtone.rbt_status as rbtStatus
				FROM ringtone
				INNER JOIN ringtone_status ON ringtone.id = ringtone_status.ringtone_id
				WHERE 
				      ringtone.creator_id = ".$userId.
					 ' AND ringtone_status.rt_approve = 2
					 AND ringtone_status.rt_delete <> 2';
		switch ($time){
			case 0: 
				$sql.= ' ORDER BY ringtone.created_datetime DESC';
				break;
			case 1: 
				$sql.= ' ORDER BY totalDownload DESC';
				break;
			case 2:
				$sql.= ' ORDER BY TRIM(ringtone.name)';
				break;
		}
		if($limit){
			$sql.= ' LIMIT '.$limit;	
		}
		if ($offset){
			$sql.=' OFFSET '.$offset;
		}
		return $con->doSelect($sql);
	}
	
	public static function getTotalDeleteRingtone_rbtByAdmin($userId){
		$con = new MysqlDAL('propel');
		$sql = "SELECT count(*) as totalRingtone
				FROM ringtone
				INNER JOIN ringtone_status ON ringtone.id = ringtone_status.ringtone_id
				WHERE 
				      ringtone.creator_id = ".$userId.
					 ' AND ringtone_status.rbt_approve = 2
					  AND ringtone_status.rt_delete <> 2';
		$totalRingtone = $con->doSelect($sql);
		if(count($totalRingtone) >0){
			$totalRingtone = $totalRingtone[0]['totalRingtone'];
		}
		return $totalRingtone;
	}    
	public static function getDeleteRingtone_rbtByAdmin($userId, $time, $limit, $offset= null){
		$con = new MysqlDAL('propel');
		$con->safe($userId);
		$sql = "SELECT 
					   SQL_CALC_FOUND_ROWS
					   ringtone.id as ringtoneId,
				       ringtone.name as ringtoneName,
				       ringtone.avatar_path as avatarPath,
				       ringtone.category_id as categoryId,
				       ringtone.category_name as categoryName,
				       ringtone.creator_id as creatorId,
				       ringtone.creator_name as creatorName,
				       ringtone.duration as duration,
				       ringtone.file_path_mp3 as filePath,
				       ringtone_status.rt_approve_description as approvedDescription,
				       UNIX_TIMESTAMP(ringtone.created_datetime) as createdDatetime,
				       ringtone.rt_status as rtStatus,
				       (ringtone.total_set + ringtone.total_download) as totalDownload,
				       ringtone.rbt_status as rbtStatus
				FROM ringtone
				INNER JOIN ringtone_status ON ringtone.id = ringtone_status.ringtone_id
				WHERE 
				      ringtone.creator_id = ".$userId.
					 ' AND ringtone_status.rbt_approve = 2
					  AND ringtone_status.rt_delete <> 2';
		switch ($time){
			case 0: 
				$sql.= ' ORDER BY ringtone.created_datetime DESC';
				break;
			case 1: 
				$sql.= ' ORDER BY totalDownload DESC';
				break;
			case 2:
				$sql.= ' ORDER BY TRIM(ringtone.name)';
				break;
		}
		if($limit){
			$sql.= ' LIMIT '.$limit;	
		}
		if ($offset){
			$sql.=' OFFSET '.$offset;
		}
		return $con->doSelect($sql);
	}
	
	public static function getTotalNotApprovedRingtone($userId){
		$con = new MysqlDAL('propel');
		$sql = "SELECT count(*) as totalRingtone
				FROM ringtone
				INNER JOIN ringtone_status ON ringtone.id = ringtone_status.ringtone_id
				WHERE 
			      ringtone.creator_id = ".$userId.
				  ' AND ringtone_status.rt_delete = 0 
				 AND ringtone_status.rt_approve = 0';
		$totalRingtone = $con->doSelect($sql);
		if(count($totalRingtone) >0){
			$totalRingtone = $totalRingtone[0]['totalRingtone'];
		}
		return $totalRingtone;
	}
	
	static public function getNotApprovedRingtone($userId, $time, $limit, $offset){
		$con = new MysqlDAL('propel');
		$con->safe($userId);
		$sql = "SELECT 
					   SQL_CALC_FOUND_ROWS
					   ringtone.id as ringtoneId,
				       ringtone.name as ringtoneName,
				       ringtone.avatar_path as avatarPath,
				       ringtone.category_id as categoryId,
				       ringtone.category_name as categoryName,
				       ringtone.creator_id as creatorId,
				       ringtone.creator_name as creatorName,
				       ringtone.duration as duration,
				       ringtone.file_path_mp3 as filePath,
				       UNIX_TIMESTAMP(ringtone.created_datetime) as createdDatetime,
				       ringtone.rt_status as rtStatus,
				       (ringtone.total_set + ringtone.total_download) as totalDownload,
				       ringtone.rbt_status as rbtStatus
				FROM ringtone
				INNER JOIN ringtone_status ON ringtone.id = ringtone_status.ringtone_id
				WHERE 
				      ringtone.creator_id = ".$userId.
					  ' AND ringtone_status.rt_delete = 0 
					 AND ringtone_status.rt_approve = 0';
		switch ($time){
			case 0: 
				$sql.= ' ORDER BY ringtone.created_datetime DESC';
				break;
			case 1: 
				$sql.= ' ORDER BY totalDownload DESC';
				break;
			case 2:
				$sql.= ' ORDER BY TRIM(ringtone.name)';
				break;
		}
		if($limit){
			$sql.= ' LIMIT '.$limit;	
		}
		if ($offset){
			$sql.=' OFFSET '.$offset;
		}
		return $con->doSelect($sql);
	}
	static public function updateUserInfo($id, $name){
			
		$con = new MysqlDAL('innodb');
		$id = $con->safe($id);
		$name = $con->safe($name);
		
		$sql = "update ringtone 
				set ringtone.creator_name = ".$name." 
				where ringtone.creator_id = ".$id;
		
		$con->doUpdate($sql);		
	}
	
	public static function getRingtoneByUserIdUsed($userId, $time, $limit, $offset= null){
		$con = new MysqlDAL('propel');
		$con->safe($userId);
		$sql = "SELECT 
					   SQL_CALC_FOUND_ROWS
					   DISTINCT		
					   ringtone.id as ringtoneId,
				       ringtone.name as ringtoneName,
				       ringtone.avatar_path as avatarPath,
				       ringtone.category_id as categoryId,
				       ringtone.category_name as categoryName,
				       ringtone.creator_id as creatorId,
				       ringtone.creator_name as creatorName,
				       ringtone.duration as duration,
				       ringtone.file_path_mp3 as filePath,
				       UNIX_TIMESTAMP(ringtone.created_datetime) as createdDatetime,
				       (ringtone.total_set + ringtone.total_download) as totalDownload,
				       ringtone.rt_status as rtStatus,
				       ringtone.rbt_status as rbtStatus
				FROM ringtone
				INNER JOIN ringtone_download 
				ON ringtone.id = ringtone_download.ringtone_id
				WHERE ringtone.rt_status = 1 AND
				      ringtone.creator_id = ".$userId; 
		switch ($time){
			case 0: 
				$sql.= ' ORDER BY createdDatetime DESC';
				break;
			case 1: 
				$sql.= ' ORDER BY totalDownload DESC';
				break;
			case 2:
				$sql.= ' ORDER BY TRIM(ringtoneName)';
				break;
		}
		if($limit){
			$sql.= ' LIMIT '.$limit;	
		}
		if ($offset){
			$sql.=' OFFSET '.$offset;
		}
		return $con->doSelect($sql);
	}
}
